#!/usr/bin/python

""" updateUserInfo.py
    Pulls info from the Grinnell directory and updates the info for each
    TC who currently resides in the DB. """

__author__ = "Dylan J. Sather"
__created__ = "2009-06-01"
__lastEdited__ = "2009-07-05"

import os
import sys
import smtplib
from xml.dom import minidom
from time import strftime
import MySQLdb

# Important constants and lists
pwebURL = "https://pioneerweb.grinnell.edu/scripts-docs/roster/feedster.asp?usr="
currentDate = strftime("%Y-%m-%d")
dataArray = ['first_name', 'last_name', 'e-mail', 'box', 'class', \
             'phone', 'OCphone', 'major', 'concentration']
tcdbArray = ['first_name', 'last_name', 'email', 'box_num', 'class', \
             'campus_phone', 'campus_phone', 'major', 'concentration']
invalidUsers = []

# Function to join all invalid names
def nameJoin():
    return ", ".join(invalidUsers)

# Connect to DB and set cursor
db = MySQLdb.connect(host="localhost", user="user", passwd="passwd", db="db")
cursor = tcdb.cursor()

# Fetch all usernames from users table
cursor.execute("SELECT username FROM users")
usernames = cursor.fetchall()

# For each username...
for user in usernames:
    
    # Fetch page from pweb, saving file as username of current user in xml/
    os.system("wget --no-check-certificate " + pwebURL + user[0] + " -O xml/" + user[0])

    # Parse XML, searching for each element in dataArray and updating each corresponding element in tcdbArray
    xmldoc = minidom.parse('xml/' + user[0])
    
    user = user[0]
    
    for i in range(len(dataArray)):
        data = xmldoc.getElementsByTagName(dataArray[i])

	# If we find an element from the above search, and if that element contains data, update info in DB
	if data:
	    if data[0].firstChild != None:
		if data[0].firstChild.data == "Invalid":
		    if user not in invalidUsers:
			invalidUsers.append(user)
	        cursor.execute("UPDATE users \
	                        SET " + tcdbArray[i] + "='" + data[0].firstChild.data + "' \
			        WHERE username='" + user[0] + "'")

    # Finally, update last_updated field in TCDB
    cursor.execute("UPDATE users \
                    SET last_updated='" + currentDate + "' \
		    WHERE username='" + user[0] + "'")        

# Send mail
fromAddress = "user.info@grinnell.edu"
toAddress = "tc@grinnell.edu"
subject = "Invalid user data was pulled from the Directory"
msg = "The script pulled invalid data for these users: " + nameJoin() + "\nThis likely means the user no longer exists"

message = """\
From: %s
To: %s
Subject: %s

%s
""" % (fromAddress, toAddress, subject, msg)
server = smtplib.SMTP('localhost')
server.sendmail(fromAddress, toAddress, message)
server.quit()

# Close up
cursor.close()
tcdb.close()
